package com.ym.utils;

import com.alibaba.fastjson.JSON;
import com.ym.utils.exception.CustomException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * @version v1.0
 * @ProjectName: SpringCloud-Alibaba-YM
 * @ClassName: ParsingExcel
 * @Description: TODO 解析Excle
 * @Author: 晨曦
 * @Date: 2020/11/14 20:51
 */

public class ExcelUtils {
    private final static Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * @throws
     * @Title ParsingExcel
     * @Description TODO(解析Excel)
     * @Params [sheetNum：起始读取sheet页, startRowNum ：起始读取行数, file, mapEntity 例 ：[{0,"name"},{1，"userNum"}]]
     * @Return void
     * @Author 晨曦
     * @Date 2020/11/15 16:55
     */
    public List<Map<String, Object>> ParsingExcel(Integer sheetNum, Integer startRowNum, MultipartFile file, TreeMap mapEntity) throws CustomException {
        log.info("ExcelUtils : sheetNum = {},startRowNum ={},mapEntityJson ={},", sheetNum, startRowNum, mapEntity);
        //读取了emp-poi.xlsx ,创建了Workbook(内存)
        //InputStream inp = new FileInputStream("C:/Users/16664/Desktop/11月cdc.xls");
        // InputStream inp = new FileInputStream(file);
        InputStream inp = null;
        Workbook wb = null;
        try {
            inp = file.getInputStream();
            wb = WorkbookFactory.create(inp);
            //拿到对应的表
            Sheet sheet = wb.getSheetAt(sheetNum);
            //拿到这表的总行数
            int lastRowNum = sheet.getLastRowNum();
            List<Map<String, Object>> resultMapList = new ArrayList<>(lastRowNum);
            for (int i = startRowNum; i <= lastRowNum; i++) {
                //拿到每一行
                Row row = sheet.getRow(i);
                //拿到这一行的总列数
                short lastCellNum = row.getLastCellNum();
                if (lastCellNum != mapEntity.keySet().size()) {
                    log.info("ExcleLastCellNum ={}，mapEntitySize ={}", lastCellNum, mapEntity.keySet().size());
                    throw new CustomException(500, "Excle总列数与Map定义Key总数不相符");
                }
                TreeMap mapRowEntity = new TreeMap();
                short readCellNum = (short) mapEntity.keySet().size();
                for (int j = 0; j < readCellNum; j++) {
                    //拿到这一个格子与它的数据
                    Cell cell = row.getCell(j);
                    if (!ObjectUtils.isEmpty(cell)) {
                        cell.setCellType(CellType.STRING);
                        mapRowEntity.put(mapEntity.get(j), cell.getStringCellValue());
                        //log.info("rowNum ={}，CellNum ={}，mapRowEntityJSON ={}", i, j, JSON.toJSONString(mapRowEntity));
                    } else {
                        mapRowEntity.put(mapEntity.get(j), null);
                    }
                }
                log.info("rowNum ={}，mapRowEntityJSON ={}", i, JSON.toJSONString(mapRowEntity));
                resultMapList.add(mapRowEntity);
            }

            return resultMapList;
        } catch (Exception e) {
            log.error(e.getMessage());
            throw new CustomException(500, e.getMessage());
        } finally {
            if (inp != null || wb != null) {
                try {
                    if (inp != null) {
                        inp.close();
                    }
                    if (wb != null) {
                        wb.close();
                    }
                } catch (Exception e) {
                    log.error("流关闭异常---> Msg={}", e.getMessage());
                }
            }
        }
    }

    /**
     * InputStream 转 File
     *
     * @param ins
     * @param file
     */
    public static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
